#include "acdb.h"
#include <QMessageBox>
#include <QObject>
#include <QApplication>
#include <QStandardItemModel>
#include <QDate>
#include "fixedfield/fixedfield.h"

AcDB::AcDB()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(QApplication::applicationDirPath() + "/acbook.sqlite");
}

AcDB::~AcDB()
{
    m_db.close();
}

bool AcDB::open()
{
    bool ok = m_db.open();
    if (!ok)
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(m_db.lastError().text()));
        return FALSE;
    }
    return TRUE;
}

bool AcDB::getMonthBrief(QDate date, QList<float>* result)
{
    Q_ASSERT(result != NULL);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "SELECT i_cash, o_cash, i_cash - o_cash n_cash, cash1 - cash2 l_cash, cash1 - cash2 + (i_cash - o_cash) a_cash "
            "FROM (SELECT total(rec_cash) i_cash FROM AccRecord, AccMainClass, AccSubClass "
            "        WHERE rec_type=cls_id and cls_mid=mc_id and (mc_name='收入' or mc_name='工资收入') "
            "        and date(rec_date)>='%1-01' and date(rec_date)<='%1-31'),"
            "     (SELECT total(rec_cash) o_cash FROM AccRecord, AccMainClass, AccSubClass "
            "        WHERE rec_type=cls_id and cls_mid=mc_id and (mc_name<>'收入' and mc_name<>'工资收入') "
            "        and date(rec_date)>='%1-01' and date(rec_date)<='%1-31'),"
            "     (SELECT total(rec_cash) cash1 FROM AccRecord, AccMainClass, AccSubClass "
            "  WHERE date(rec_date)<'%1-01' and rec_type=cls_id and cls_mid=mc_id and (mc_name='收入' or mc_name='工资收入')), "
            "     (SELECT total(rec_cash) cash2 FROM AccRecord, AccMainClass, AccSubClass "
            "  WHERE date(rec_date)<'%1-01' and rec_type=cls_id and cls_mid=mc_id and (mc_name<>'收入' and mc_name<>'工资收入'))")
            .arg(date.toString("yyyy-MM"));
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    if (query.next())
    {
        result->append(query.value(0).toFloat());
        result->append(query.value(1).toFloat());
        result->append(query.value(2).toFloat());
        result->append(query.value(3).toFloat());
        result->append(query.value(4).toFloat());
    } else {
        result->append(0.0);
        result->append(0.0);
        result->append(0.0);
        result->append(0.0);
        result->append(0.0);
    }

    return TRUE;
}

bool AcDB::getDayBrief(QDate date, QList<float>* result)
{
    Q_ASSERT(result != NULL);

    QSqlQuery query(m_db);
    QString sql = QString(
            "SELECT * FROM "
            "(SELECT total(rec_cash) in_cash "
            "FROM AccRecord, AccSubClass, AccMainClass "
            "WHERE mc_type=1 and rec_type=cls_id and cls_mid=mc_id and date(rec_date) = '%1'), "
            "(SELECT total(rec_cash) out_cash "
            "FROM AccRecord, AccSubClass, AccMainClass "
            "WHERE mc_type=0 and rec_type=cls_id and cls_mid=mc_id and date(rec_date) = '%1')")
            .arg(date.toString("yyyy-MM-dd"));
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    if (query.next())
    {
        result->append(query.value(0).toFloat());
        result->append(query.value(1).toFloat());
    } else {
        result->append(0.0);
        result->append(0.0);
    }

    return true;
}

bool AcDB::getMonthSalary(QDate date, QList<float>* result)
{
    Q_ASSERT(result != NULL);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "SELECT * FROM "
            "(SELECT rec_cash mcash "
            "FROM AccRecord, AccSubClass, AccMainClass "
            "WHERE cls_mid=mc_id and mc_name='工资收入' and cls_name='夫工资' "
            "and rec_type=cls_id and date(rec_date) >= '%1-01' and date(rec_date) <='%1-31'),"
            "(SELECT rec_cash fcash "
            "FROM AccRecord, AccSubClass, AccMainClass "
            "WHERE cls_mid=mc_id and mc_name='工资收入' and cls_name='妻工资' "
            "and rec_type=cls_id and date(rec_date) >= '%1-01' and date(rec_date) <='%1-31')")
            .arg(date.toString("yyyy-MM"));
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    if (query.next())
    {
        result->append(query.value(0).toFloat());
        result->append(query.value(1).toFloat());
    } else {
        result->append(0.0);
        result->append(0.0);
    }

    return true;
}

bool AcDB::getDayDataset(QDate date, QAbstractItemModel* model)
{
    Q_ASSERT(model != NULL);

    QStandardItemModel* sm = (QStandardItemModel*)model;

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "SELECT date(rec_date) rec_date, rec_name, rec_cash cash, rec_id, '['||mc_name||'] '||cls_name cls_name "
            "FROM AccRecord, AccSubClass, AccMainClass "
            "WHERE mc_name<>'固定支出' and mc_name<>'工资收入' and rec_type=cls_id and cls_mid=mc_id and date(rec_date) = '%1'")
            .arg(date.toString("yyyy-MM-dd"));
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    int numRows = 0;
    if (m_db.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        numRows = query.size();
    }
    else
    {
        query.last();
        numRows = query.at() + 1;
        query.seek(-1);
    }

    sm->setRowCount(0);
    if (numRows > 0)
    {
        sm->setRowCount(numRows);
        int row = 0;
        while(query.next())
        {
            sm->setItem(row, 0, new QStandardItem(query.value(1).toString()));
            sm->setItem(row, 1, new QStandardItem(query.value(4).toString()));
            sm->setItem(row, 2, new QStandardItem(query.value(2).toString()));
            sm->setItem(row, 4, new QStandardItem(query.value(3).toString()));
            row++;
        }
    } else {
        sm->setRowCount(0);
    }

    return TRUE;
}

bool AcDB::getItem(int id, QList<QVariant>* result)
{
    Q_ASSERT(result != NULL);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "SELECT rec_name, rec_cash, rec_type, date(rec_date), rec_remark, rec_att, rec_id FROM AccRecord WHERE rec_id = '%1'")
            .arg(id);
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    if (query.next())
    {
        result->append(query.value(0).toString());
        result->append(query.value(1).toFloat());
        result->append(query.value(2).toInt());
        result->append(query.value(3).toDate());
        result->append(query.value(4).toString());
        //result->append(query.value(5).toFloat());
    } else {
        result->append("");
        result->append(0.0);
        result->append(0);
        result->append(QDate());
        result->append("");
        //result->append(0.0);
    }

    return TRUE;
}

bool AcDB::delItem(int id)
{
    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "DELETE FROM AccRecord WHERE rec_id = '%1'")
            .arg(id);
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    return TRUE;
}

bool AcDB::listClass(QList< QList<QVariant> >* result)
{
    Q_ASSERT(result != NULL);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8(
            "SELECT cls_id data, '['||mc_name||'] '||cls_name label "
            "FROM AccSubClass, AccMainClass "
            "WHERE cls_mid=mc_id and mc_name<>'固定支出' and mc_name<>'工资收入' "
            "ORDER BY mc_id;");
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    while (query.next())
    {
        QList<QVariant> data;
        data.append(query.value(0).toInt());
        data.append(query.value(1).toString());
        result->append(data);
    }
    if (result->size() == 0)
    {
        QList<QVariant> data;
        data.append(0);
        data.append("");
        result->append(data);
    }

    return TRUE;
}

bool AcDB::addItem(const QList<QVariant> *data)
{
    Q_ASSERT(data != NULL && data->size() >= 4);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8("INSERT INTO AccRecord(rec_name, rec_type, rec_date, rec_cash) "
                                     "VALUES('%1', %2, '%3', %4)")
                  .arg(data->at(0).toString())
                  .arg(data->at(1).toInt())
                  .arg(data->at(2).toDate().toString("yyyy-MM-dd"))
                  .arg(data->at(3).toFloat());
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    return TRUE;
}

bool AcDB::updateItem(int id, const QList<QVariant> *data)
{
    Q_ASSERT(data != NULL && data->size() >= 5);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8("UPDATE AccRecord SET rec_name='%1',"
                                     "rec_type=%2,"
                                     "rec_date='%3',"
                                     "rec_cash=%4,"
                                     "rec_remark='%5' WHERE rec_id=%6")
                  .arg(data->at(0).toString())
                  .arg(data->at(1).toInt())
                  .arg(data->at(2).toDate().toString("yyyy-MM-dd"))
                  .arg(data->at(3).toFloat())
                  .arg(data->at(4).toString())
                  .arg(id);
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    return TRUE;
}

bool AcDB::updateSalary(QDate *date, float cost, int type)
{
    Q_ASSERT(date != NULL);

    QString cls, year_month;

    if (type == 1)
    {
        cls = QString().fromUtf8("夫工资");
    } else {
        cls = QString().fromUtf8("妻工资");
    }

    year_month = date->toString("yyyy-MM");

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8("SELECT rec_name, rec_cash cash, rec_id "
                                     "FROM AccRecord, AccSubClass, AccMainClass "
                                     "WHERE cls_mid=mc_id and mc_name='工资收入' and cls_name='%1' "
                                     "and rec_type=cls_id and date(rec_date) >= '%2-1' and date(rec_date) <='%2-31'")
                  .arg(cls)
                  .arg(year_month);
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    } else {
        if (query.next())
        {
            query.prepare(QString().fromUtf8("UPDATE AccRecord SET rec_cash=%1 WHERE rec_id=%2")
                          .arg(cost)
                          .arg(query.value(2).toInt()));

            if (!query.exec())
            {
                QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
                return FALSE;
            }

        } else {
            query.prepare(QString().fromUtf8("INSERT INTO AccRecord(rec_name, rec_cash, rec_type, rec_date) "
                                             "SELECT '%1', %2, cls_id, '%3' FROM AccMainClass, AccSubClass "
                                             "WHERE cls_mid=mc_id and mc_name='工资收入' and cls_name='%1'")
                          .arg(cls)
                          .arg(cost)
                          .arg(date->toString("yyyy-MM-dd")));

            if (!query.exec())
            {
                QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
                return FALSE;
            }
        }
    }

    return TRUE;
}

bool AcDB::getFixedItemList(QLayout* layout)
{
    Q_ASSERT(layout != NULL);

    QSqlQuery query(m_db);
    QString sql = QString().fromUtf8("SELECT cls_name, cls_id "
                                     "FROM AccMainClass, AccSubClass "
                                     "WHERE cls_mid=mc_id and mc_name='固定支出'"
                                     "ORDER BY cls_id");
    query.prepare(sql);

    if (!query.exec())
    {
        QMessageBox::critical(NULL, QObject::tr("数据库错误"), QObject::tr("数据库无法打开:").append(query.lastError().text()));
        return FALSE;
    }

    int numRows = 0;
    if (m_db.driver()->hasFeature(QSqlDriver::QuerySize))
    {
        numRows = query.size();
    }
    else
    {
        query.last();
        numRows = query.at() + 1;
        query.seek(-1);
    }

    if (numRows > 0)
    {
        while(query.next())
        {
            layout->addWidget(new FixedField(query.value(0).toString(), 2.3));
        }
    }


    return TRUE;
}
